﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using BaseData;
using BaseData.Common;
using TaxEntity.House;

namespace TaxData.House
{
    /// <summary>
    /// 承租人信息
    /// </summary>
    public class RentInfoData
    {
        /// <summary>
        /// 获取承租人信息实体
        /// </summary>
        public RentInfoEntity GetEntityById(long ID)
        {
            string sql = "select * from RentInfo with(nolock) where ID = @ID and status=1";
            SqlParameter p = new SqlParameter("@ID", SqlDbType.BigInt, 16);
            p.Value = ID;
            DataTable dt = DataConfig.QueryRecords(BusinessType.DBRead, sql, p);
            RentInfoEntity model = this.GetEntityHelper(dt);
            return model;
        }
        /// <summary>
        /// 获取承租人信息实体
        /// </summary>
        /// <param name="certificateID">房产证号</param>
        /// <param name="year">年份</param>
        /// <returns>承租人信息</returns>
        public RentInfoEntity GetEntityById(string certificateID, int year)
        {
            string sql = "select * from RentInfo with(nolock) where CertificateID = @CertificateID and status=1 and Year(startdate)=@year order by StartDate asc";
            SqlParameter p = new SqlParameter("@CertificateID", SqlDbType.VarChar, 25);
            p.Value = certificateID;
            SqlParameter p1 = new SqlParameter("@year", SqlDbType.Int, 8);
            p1.Value = year;
            DataTable dt = DataConfig.QueryRecords(BusinessType.DBRead, sql, p, p1);

            RentInfoEntity model = this.GetEntityHelper(dt);
            return model;
        }

        /// <summary>
        /// 删除承租人
        /// </summary>
        public bool Delete(long ID)
        {
            string sql = "update RentInfo set status=0 where ID = @ID";
            SqlParameter p = new SqlParameter("@ID", SqlDbType.BigInt, 16);
            p.Value = ID;
            int affectRows = DataConfig.QueryNoRecords(BusinessType.DBWrite, sql, p);
            return affectRows > 0;
        }

        /// <summary>
        /// 获取承租人列表
        /// </summary>
        public DataTable GetList(string strWhere, int pageSize, int page, ref int allCount)
        {
            DataTable dt = new DataTable();
            string condition = " where status=1" + strWhere;
            string sqlCount = "select count(1) from RentInfo with(nolock) " + condition;
            allCount = (int)DataConfig.GetSingle(BusinessType.DBRead, sqlCount);
            if (allCount > 0)
            {
                int startNum = (page - 1) * pageSize + 1;
                int endNum = page * pageSize;
                string sqlInner = "select *"//,departmentname = (select top 1 Name from Department b with(nolock) where a.DepartmentID=b.ID)"
                    //+ ",diduanname = (select top 1 name from DiDuan b with(nolock) where a.diduanid=b.id) "
                    + ",ROW_NUMBER() over(order by AddTime desc) as RowNumber from RentInfo a with(nolock) " + condition;
                string sqlUserList = string.Format("SELECT * FROM ({0}) T WHERE T.RowNumber BETWEEN {1} AND {2} order by RowNumber asc", sqlInner, startNum, endNum);
                dt = DataConfig.QueryRecords(BusinessType.DBRead, sqlUserList);
            }
            return dt;
        }

        /// <summary>
        /// 增加承租人
        /// </summary>
        /// <param name="model">承租人信息实体</param>
        /// <returns>成功承租人编号,失败返回0</returns>
        public long Add(RentInfoEntity model)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSql1 = new StringBuilder();
            StringBuilder strSql2 = new StringBuilder();
            if (model.CertificateID != null)
            {
                strSql1.Append("CertificateID,");
                strSql2.Append("'" + model.CertificateID + "',");
            }
            if (model.Name != null)
            {
                strSql1.Append("Name,");
                strSql2.Append("'" + model.Name + "',");
            }
            if (model.IDCardNum != null)
            {
                strSql1.Append("IDCardNum,");
                strSql2.Append("'" + model.IDCardNum + "',");
            }
            if (model.RentName != null)
            {
                strSql1.Append("RentName,");
                strSql2.Append("'" + model.RentName + "',");
            }
            if (model.RentIDCardNum != null)
            {
                strSql1.Append("RentIDCardNum,");
                strSql2.Append("'" + model.RentIDCardNum + "',");
            }
            if (model.RentMobileCode != null)
            {
                strSql1.Append("RentMobileCode,");
                strSql2.Append("'" + model.RentMobileCode + "',");
            }
            if (model.RentTelephone != null)
            {
                strSql1.Append("RentTelephone,");
                strSql2.Append("'" + model.RentTelephone + "',");
            }
            if (model.RentArea != null)
            {
                strSql1.Append("RentArea,");
                strSql2.Append("" + model.RentArea + ",");
            }
            if (model.RentPrice != null)
            {
                strSql1.Append("RentPrice,");
                strSql2.Append("" + model.RentPrice + ",");
            }
            if (model.ShopName != null)
            {
                strSql1.Append("ShopName,");
                strSql2.Append("'" + model.ShopName + "',");
            }
            if (model.StartDate != null)
            {
                strSql1.Append("StartDate,");
                strSql2.Append("'" + model.StartDate + "',");
            }
            if (model.EndDate != null)
            {
                strSql1.Append("EndDate,");
                strSql2.Append("'" + model.EndDate + "',");
            }
            if (model.TaxStatus != null)
            {
                strSql1.Append("TaxStatus,");
                strSql2.Append("" + model.TaxStatus + ",");
            }
            if (model.LimitDays != null)
            {
                strSql1.Append("LimitDays,");
                strSql2.Append("'" + model.LimitDays + "',");
            }
            if (model.OptUserID != null)
            {
                strSql1.Append("OptUserID,");
                strSql2.Append("" + model.OptUserID + ",");
            }
            if (model.Status != null)
            {
                strSql1.Append("Status,");
                strSql2.Append("" + model.Status + ",");
            }
            if (model.AddTime != null)
            {
                strSql1.Append("AddTime,");
                strSql2.Append("'" + model.AddTime + "',");
            }
            if (model.HouseType2 != null)
            {
                strSql1.Append("HouseType2,");
                strSql2.Append("'" + model.HouseType2 + "',");
            }
            if (model.ModifyTime != null)
            {
                strSql1.Append("ModifyTime,");
                strSql2.Append("'" + model.ModifyTime + "',");
            }
            strSql.Append("insert into RentInfo(");
            strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1));
            strSql.Append(")");
            strSql.Append(" values (");
            strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1));
            strSql.Append(")");
            strSql.Append(";select @@IDENTITY");
            object obj = DataConfig.GetSingle(BusinessType.DBWrite, strSql.ToString());
            long ID = 0;
            if (obj != null && obj != DBNull.Value)
            {
                long.TryParse(obj.ToString(), out ID);
            }
            return ID;
        }

        /// <summary>
        /// 修改承租人信息
        /// </summary>
        /// <param name="model">修改承租人</param>
        /// <returns>操作结果</returns>
        public bool Update(RentInfoEntity model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update RentInfo set ");
            if (model.RentName != null)
            {
                strSql.Append("RentName='" + model.RentName + "',");
            }
            if (model.RentIDCardNum != null)
            {
                strSql.Append("RentIDCardNum='" + model.RentIDCardNum + "',");
            }
            if (model.RentMobileCode != null)
            {
                strSql.Append("RentMobileCode='" + model.RentMobileCode + "',");
            }
            if (model.RentTelephone != null)
            {
                strSql.Append("RentTelephone='" + model.RentTelephone + "',");
            }
            if (model.RentArea != null)
            {
                strSql.Append("RentArea=" + model.RentArea + ",");
            }
            if (model.RentPrice != null)
            {
                strSql.Append("RentPrice=" + model.RentPrice + ",");
            }
            if (model.ShopName != null)
            {
                strSql.Append("ShopName='" + model.ShopName + "',");
            }
            if (model.StartDate != null)
            {
                strSql.Append("StartDate='" + model.StartDate + "',");
            }
            if (model.EndDate != null)
            {
                strSql.Append("EndDate='" + model.EndDate + "',");
            }
            if (model.TaxStatus != null)
            {
                strSql.Append("TaxStatus=" + model.TaxStatus + ",");
            }
            if (model.LimitDays != null)
            {
                strSql.Append("LimitDays='" + model.LimitDays + "',");
            }
            if (model.OptUserID != null)
            {
                strSql.Append("OptUserID=" + model.OptUserID + ",");
            }
            if (model.Status != null)
            {
                strSql.Append("Status=" + model.Status + ",");
            }
            if (model.HouseType2 != null)
            {
                strSql.Append("HouseType2='" + model.HouseType2 + "',");
            }
            if (model.ModifyTime != null)
            {
                strSql.Append("ModifyTime='" + model.ModifyTime + "',");
            }
            int n = strSql.ToString().LastIndexOf(",");
            strSql.Remove(n, 1);
            strSql.Append(" where Id=" + model.Id + "");
            int rows = DataConfig.QueryNoRecords(BusinessType.DBWrite, strSql.ToString());
            return rows > 0;
        }

        /// <summary>
        /// 从DataTable中得到一个实体对象
        /// </summary>
        private RentInfoEntity GetEntityHelper(DataTable dt)
        {
            RentInfoEntity model = null;
            if (dt != null && dt.Rows.Count > 0)
            {
                model = new RentInfoEntity();
                if (dt.Rows[0]["Id"] != null && dt.Rows[0]["Id"].ToString() != "")
                {
                    model.Id = long.Parse(dt.Rows[0]["Id"].ToString());
                }
                if (dt.Rows[0]["CertificateID"] != null && dt.Rows[0]["CertificateID"].ToString() != "")
                {
                    model.CertificateID = dt.Rows[0]["CertificateID"].ToString();
                }
                if (dt.Rows[0]["Name"] != null && dt.Rows[0]["Name"].ToString() != "")
                {
                    model.Name = dt.Rows[0]["Name"].ToString();
                }
                if (dt.Rows[0]["IDCardNum"] != null && dt.Rows[0]["IDCardNum"].ToString() != "")
                {
                    model.IDCardNum = dt.Rows[0]["IDCardNum"].ToString();
                }
                if (dt.Rows[0]["RentName"] != null && dt.Rows[0]["RentName"].ToString() != "")
                {
                    model.RentName = dt.Rows[0]["RentName"].ToString();
                }
                if (dt.Rows[0]["RentIDCardNum"] != null && dt.Rows[0]["RentIDCardNum"].ToString() != "")
                {
                    model.RentIDCardNum = dt.Rows[0]["RentIDCardNum"].ToString();
                }
                if (dt.Rows[0]["RentMobileCode"] != null && dt.Rows[0]["RentMobileCode"].ToString() != "")
                {
                    model.RentMobileCode = dt.Rows[0]["RentMobileCode"].ToString();
                }
                if (dt.Rows[0]["RentTelephone"] != null && dt.Rows[0]["RentTelephone"].ToString() != "")
                {
                    model.RentTelephone = dt.Rows[0]["RentTelephone"].ToString();
                }
                if (dt.Rows[0]["RentArea"] != null && dt.Rows[0]["RentArea"].ToString() != "")
                {
                    model.RentArea = decimal.Parse(dt.Rows[0]["RentArea"].ToString());
                }
                if (dt.Rows[0]["RentPrice"] != null && dt.Rows[0]["RentPrice"].ToString() != "")
                {
                    model.RentPrice = decimal.Parse(dt.Rows[0]["RentPrice"].ToString());
                }
                if (dt.Rows[0]["ShopName"] != null && dt.Rows[0]["ShopName"].ToString() != "")
                {
                    model.ShopName = dt.Rows[0]["ShopName"].ToString();
                }
                if (dt.Rows[0]["StartDate"] != null && dt.Rows[0]["StartDate"].ToString() != "")
                {
                    model.StartDate = DateTime.Parse(dt.Rows[0]["StartDate"].ToString());
                }
                if (dt.Rows[0]["EndDate"] != null && dt.Rows[0]["EndDate"].ToString() != "")
                {
                    model.EndDate = DateTime.Parse(dt.Rows[0]["EndDate"].ToString());
                }
                if (dt.Rows[0]["TaxStatus"] != null && dt.Rows[0]["TaxStatus"].ToString() != "")
                {
                    model.TaxStatus = int.Parse(dt.Rows[0]["TaxStatus"].ToString());
                }
                if (dt.Rows[0]["LimitDays"] != null && dt.Rows[0]["LimitDays"].ToString() != "")
                {
                    model.LimitDays = dt.Rows[0]["LimitDays"].ToString();
                }
                if (dt.Rows[0]["OptUserID"] != null && dt.Rows[0]["OptUserID"].ToString() != "")
                {
                    model.OptUserID = long.Parse(dt.Rows[0]["OptUserID"].ToString());
                }
                if (dt.Rows[0]["Status"] != null && dt.Rows[0]["Status"].ToString() != "")
                {
                    model.Status = int.Parse(dt.Rows[0]["Status"].ToString());
                }
                if (dt.Rows[0]["AddTime"] != null && dt.Rows[0]["AddTime"].ToString() != "")
                {
                    model.AddTime = DateTime.Parse(dt.Rows[0]["AddTime"].ToString());
                }
                if (dt.Rows[0]["HouseType2"] != null && dt.Rows[0]["HouseType2"].ToString() != "")
                {
                    model.HouseType2 = dt.Rows[0]["HouseType2"].ToString();
                }
                if (dt.Rows[0]["ModifyTime"] != null && dt.Rows[0]["ModifyTime"].ToString() != "")
                {
                    model.ModifyTime = DateTime.Parse(dt.Rows[0]["ModifyTime"].ToString());
                }
                return model;
            }
            return model;
        }
    }
}
